/*(c) Copyright IBM Corp. 2008  All rights reserved.                  */
 /*                                                                   */
 /*This sample program is owned by International Business Machines    */
 /*Corporation or one of its subsidiaries ("IBM") and is copyrighted  */
 /*and licensed, not sold.                                            */
 /*                                                                   */
 /*You may copy, modify, and distribute this sample program in any    */
 /*form without payment to IBM,  for any purpose including developing,*/
 /*using, marketing or distributing programs that include or are      */
 /*derivative works of the sample program. Licenses to IBM patents    */
 /*are expressly excluded from this license.                          */
 /*The sample program is provided to you on an "AS IS" basis, without */
 /*warranty of any kind.  IBM HEREBY  EXPRESSLY DISCLAIMS ALL         */
 /*WARRANTIES EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO*/
 /*THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTIC-*/
 /*ULAR PURPOSE. Some jurisdictions do not allow for the exclusion or */
 /*limitation of implied warranties, so the above limitations or      */
 /*exclusions may not apply to you.  IBM shall not be liable for any  */
 /*damages you suffer as a result of using, modifying or distributing */
 /*the sample program or its derivatives.                             */
 /*                                                                   */
 /*Each copy of any portion of this sample program or any derivative  */
 /*work,  must include a the above copyright notice and disclaimer of */
 /*warranty.                                                          */
 /*                                                                   */
 /*********************************************************************/

package tests.database.examples;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class BasicJDBCApp {

	Connection connection = null;

	PreparedStatement pStatement = null;
	

	private BasicJDBCApp(String URL, String userID, String password) {

		try {

			/* 
			 * load driver and connect to database
			 */
			Class.forName("com.ibm.db2.jcc.DB2Driver");
			connection = DriverManager.getConnection(URL, userID, password);

		}
		catch(Exception ex) {
			System.out.println(ex.getMessage());
			ex.printStackTrace(System.out);
		}
	}

	private void runCodePath1() {

		if(connection == null)
			return;

		System.out.println("*** Executing code path 1 ***");

		PreparedStatement statement = null;
		ResultSet resultSet = null;

		try {


			String strSQL = "SELECT P.PRODUCT_NUMBER, PNL.PRODUCT_NAME " +
			"FROM GOSALES.PRODUCT_NAME_LOOKUP PNL, GOSALES.PRODUCT P " +
			"WHERE PNL.PRODUCT_NUMBER = P.PRODUCT_NUMBER " +
			"AND PNL.PRODUCT_LANGUAGE = ?";
			statement = connection.prepareStatement(strSQL);
			statement.setString(1, "EN");
			if(statement.execute()){
				resultSet = statement.getResultSet();
				while(resultSet.next()){
					System.out.println(resultSet.getString("PRODUCT_NAME"));
				}
			}
			System.out.println();

			resultSet.close();
			statement.close();

			strSQL = "SELECT P.PRODUCT_NUMBER, PNL.PRODUCT_NAME, (P.PRODUCTION_COST * 1.1) AS PRODUCT_PRICE " +
			"FROM GOSALES.PRODUCT_NAME_LOOKUP AS PNL, GOSALES.PRODUCT AS P, GOSALES.PRODUCT_BRAND AS PB " +
			"WHERE PNL.PRODUCT_NUMBER = P.PRODUCT_NUMBER AND PB.PRODUCT_BRAND_CODE = P.PRODUCT_BRAND_CODE AND " +
			"PB.PRODUCT_BRAND_EN = ? AND PNL.PRODUCT_LANGUAGE = ?";
			statement = connection.prepareStatement(strSQL);
			statement.setString(1, "Extreme");
			statement.setString(2,"EN");
			if(statement.execute()) {
				resultSet = statement.getResultSet();
				while(resultSet.next()) {
					System.out.print(resultSet.getString("PRODUCT_NAME") + " ");
					System.out.println(resultSet.getString("PRODUCT_PRICE"));					
				}
			}
			System.out.println();

			resultSet.close();
			statement.close();			

		}
		catch(Exception ex) {
			System.out.println(ex.getMessage());
			ex.printStackTrace(System.out);
		}
		finally {

			if(resultSet != null) {
				try {
					resultSet.close();
				}
				catch(Exception ex) {
					// ignore
				}
			}
			if(statement != null)
				try {
					statement.close();
				}
			catch(Exception ex) {
				// ignore
			}

		}
	}


	private void runCodePath2() {

		if(connection == null)
			return;

		System.out.println("*** Executing code path 2 ***");

		PreparedStatement pStatement = null;
		ResultSet resultSet = null;

		try {


			/*
			 * 	 plain SQL statements
			 */
			
			String sql = "SELECT CUST_FRST_NAME, CUST_CITY FROM GOSALESCT.CUST " +			
			"WHERE CUST_CITY = ?";

			pStatement = connection.prepareStatement(sql);			
			pStatement.setString(1, "San Jose");
			if(pStatement.execute()){
				resultSet = pStatement.getResultSet();
				while(resultSet.next()) {
					System.out.println(resultSet.getString("CUST_FRST_NAME") + " " +
							resultSet.getString("CUST_CITY"));					
				}
			}

			System.out.println();
			resultSet.close();			

			sql= "SELECT COUNT(CUST_CODE) AS NUMBER_OF_CUST FROM GOSALESCT.CUST";

			pStatement = connection.prepareStatement(sql);
			if(pStatement.execute()){
				resultSet = pStatement.getResultSet();
				while(resultSet.next()) {
					System.out.println("Number of customers: " + resultSet.getString("NUMBER_OF_CUST"));
				}
				resultSet.close();
			}			
			pStatement.close();			
		}
		catch(Exception ex) {
			System.out.println(ex.getMessage());
			ex.printStackTrace(System.out);
		}
		finally {

			if(resultSet != null) {
				try {
					resultSet.close();
				}
				catch(Exception ex) {
					// ignore
				}
			}
			if(pStatement != null)
				try {
					pStatement.close();
				}
			catch(Exception ex) {
				// ignore
			}			
		}

	}

	private void cleanup() {

		if(connection != null) {
			try {
				connection.close();
			}
			catch(Exception ex) {
				// ignore
			}
		}

	} // end method


	/**
	 * @param args 1) db name 2) user id 3) password
	 */
	public static void main(String[] args) {

		if(args.length < 3) {
			System.out.println("SimpleJDBCApp <dbURL> <user id> <password>");
			System.exit(1);
		}

		BasicJDBCApp application = null; 

		try {

			application = new BasicJDBCApp(args[0],args[1],args[2]);

			if(args.length == 4) {
				if("1".equals(args[3]))
					application.runCodePath1();
				else
					if("2".equals(args[3]))
						application.runCodePath2();

			}
			else {
				application.runCodePath1();
				application.runCodePath2();
			}

		}
		catch(Exception ex) {
			System.out.println(ex.getMessage());
			ex.printStackTrace(System.out);
		}
		finally {
			if(application != null)
				application.cleanup();
		}
	}
}
